/*
 * IIS Forencie. 
 * 
 * Code Based on the article Forensic Log Parsing with Microsoft's LogParser
 * by Mark Burnett 2003 - 07 - 18
 * 
 * http://www.securityfocus.com/infocus/1712
 */


using System;
using System.Configuration;
using System.Web;
using System.Data;

using MSUtil;

using log4net;
using log4net.Config;

namespace AjaxLabCmd.Objects.PlugIns
{
	public class IISLogParser
	{
		private static readonly ILog log = LogManager.GetLogger(typeof(CMDLogParserDemo));

		private DataSet parseLog(string query)
		{
			LogQueryClassClass logParser = new LogQueryClassClass();
			COMIISW3CInputContextClassClass iisLog = new COMIISW3CInputContextClassClass();

			ILogRecordset rsLP = null;
			ILogRecord rowLP = null;
			
			log.Debug("IIS Log Parser query:"+query);
			rsLP = logParser.Execute(query, iisLog);

			DataTable tab = new DataTable("Results");

			// copy schema
			for (int i = 0; i < rsLP.getColumnCount(); i++)
			{
				DataColumn col = new DataColumn();
				col.ColumnName = rsLP.getColumnName(i);
				switch (rsLP.getColumnType(i))
				{
					case 1:
						col.DataType = Type.GetType("System.Int32");
						break;
					case 2:
						col.DataType = Type.GetType("System.Double");
						break;
					case 4:
						col.DataType = Type.GetType("System.DateTime");
						break;
					default:
						col.DataType = Type.GetType("System.String");
						break;
				}
				tab.Columns.Add(col);
			}

			// copy data
			while (!rsLP.atEnd())
			{
				rowLP = rsLP.getRecord();
				DataRow row = tab.NewRow();

				for (int i = 0; i < rsLP.getColumnCount(); i++)
					row[i] = HttpUtility.HtmlEncode(Convert.ToString(rowLP.getValue(i)));

				tab.Rows.Add(row);
				rsLP.moveNext();
			}

			DataSet ds = new DataSet();
			ds.Tables.Add(tab);

			return ds;
		}

		/*
		 * My own query. Just want to see who visited the most
		 */
		public DataSet SelectTopVisits(String X)
		{
			string strSQL = null;
			string log_dir = "";
			
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	

			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			strSQL = @"SELECT Top "+X+" c-ip as IP, Count(c-ip) as Total from "+log_dir+"*.log" +
				@" group by c-ip order by Total Desc";
			
			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}


		/*
		 *	But suppose the attacker was careful and deleted all Trojan files when finished. In that case, the files will not be exist but there will be log entries showing successful requests for those files. To identify these log entries you must make a list of all files on your site that have resulted in 200 HTTP status codes. From your log files directory, execute the following query:
		 */
		public DataSet SelectSuccessfulAttemps(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
					X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			strSQL = @"SELECT Top "+X+" DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits from "+log_dir+"*.log" +
				@" WHERE sc-status=200 GROUP BY URL ORDER BY URL";
			
			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;

		}

		/*
		 *The following query will show the number of hits for each day for each ASP and DLL file. From your log files directory, type the following:
		 */
		public DataSet PageHits(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd') AS Day, cs-uri-stem As PageName, COUNT(*) AS Total from "+log_dir+"*.log" +
					 @" WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' OR TO_LOWERCASE(cs-uri-stem) LIKE '%.html%') GROUP BY Day, cs-uri-stem ORDER BY cs-uri-stem, Day";

			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;

		}

		/*
		 *Another good attack indicator is the number of errors per hour. The following script returns the dates and hours that had more than 25 error codes returned. This value will likely need adjusting depending on how much traffic your site receives:
		 */
		public DataSet PageErrors(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" date, QUANTIZE(time, 3600) AS hour, sc-status as status, Count(*) AS errors from "+log_dir+"*.log" +
				@" WHERE sc-status>=400 GROUP BY date, hour, sc-status HAVING Errors>25 ORDER BY Errors DESC";

			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;

		}

		/*
		 *Further investigation of the dates listed above may show that the high number of 404 errors are CGI scans looking for vulnerable scripts on your site. The 404 errors themselves are not as much as a concern as are the 200 results during that same time that may indicate a successful attack. This query will return all valid requests from any IP address that also had a 404 error 
		 */
		public DataSet PossibleAttacks(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" c-ip as IP, cs-uri-stem as PageName, Count(*) as Hits from "+log_dir+"*.log" +
				@" Where TO_LOWERCASE(cs-uri-stem) NOT LIKE '%.gif' AND TO_LOWERCASE(cs-uri-stem) NOT LIKE '%.jpg%' AND c-ip IN (SELECT c-ip FROM "+log_dir+"*.log" +" WHERE sc-status=404) AND sc-status=200 GROUP BY c-ip, cs-uri-stem";

			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;

		}

		/*
		 *. It could be that these were two different attackers or the same attacker who used two different proxies to conceal his or her IP address. One way to find out if they are likely the same person is to check the User-Agent header for the two different IP addresses:
		 */
		public DataSet CompareIPsUserAgent(String IP1, String IP2)
		{
			string strSQL = null;
			string log_dir = "";
	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT DISTINCT c-ip as IP, cs(User-Agent) as Agent from "+log_dir+"*.log" +
				@" WHERE c-ip='"+IP1+"' or c-ip='"+IP2+"'";

			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 *It may also be useful to see an unusually high number of hits on a single page from a single IP address. The following query shows any IP address that hit the same page more than 50 times in a single day:
		 */

		public DataSet IPHitsPerPage(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" DISTINCT date, cs-uri-stem as PageName, c-ip as IP, Count(*) AS Hits from "+log_dir+"*.log" +
				@" GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 * Another useful technique is to view exactly what ASP errors IIS encountered while serving requests. Most attempts at breaking into a web site will inevitably result in some kind of error. The following query will return a list of every ASP error recorded in the log files:
		 */
		public DataSet RecordedErrors(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-query as URIQuery, Count(*) AS Total from "+log_dir+"*.log" +
				@" WHERE sc-status>=500 GROUP BY cs-uri-query ORDER BY Total DESC";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 *Another way to identify errors is to look at the status codes returned by the server. If you want to see a detail of what status codes IIS returned for each page, try the following query:
		 */
		public DataSet BrokenLinks(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" date, QUANTIZE(time, 3600) AS hour, sc-status as Status, Count(*) AS Errors  from "+log_dir+"*.log" +
				@" WHERE sc-status>=400 GROUP BY date, hour, sc-status HAVING Errors>25 ORDER BY Errors DESC";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 *Also of interest are the Win32 Status codes, which may be attack indicators:
		 */
		public DataSet Win32Errors(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-stem as URI, WIN32_ERROR_DESCRIPTION(sc-win32-status) as Error, Count(*) AS Total from "+log_dir+"*.log" +
				@" WHERE sc-win32-status>0 and (TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, Error ORDER BY cs-uri-stem, Error";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 * Some ASP pages should only accept form input from previous pages. If, for example, you may have a page such as checkout1.asp that sends a POST request to checkout2.asp, then anything other than a POST request to checkout2.asp may be suspicious. This query will show what HTTP methods were sent to each page:
		 */
		public DataSet PageHttpMethods(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-stem as URI, cs-method as Method, Count(*) AS Total from "+log_dir+"*.log" +
				@" WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%') GROUP BY cs-uri-stem, cs-method ORDER BY cs-uri-stem, cs-method";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}		

		/*
		 * The following query will report some statistics for the number of bytes sent to the client
		 */
		public DataSet BytesSendToClient(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-stem as URI, Count(*) as Hits, AVG(sc-bytes) AS Avg, Max(sc-bytes) AS Max, Min(sc-bytes) AS Min, Sum(sc-bytes) AS Total from "+log_dir+"*.log" +
				@" WHERE TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%' GROUP BY cs-uri-stem ORDER BY cs-uri-stem";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 * And this one will report on bytes sent from the client:
		 */

		public DataSet BytesSendFromClient(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-stem as URI, Count(*) as Hits, AVG(cs-bytes) AS Avg, Max(cs-bytes) AS Max, Min(cs-bytes) AS Min, Sum(cs-bytes) AS Total from "+log_dir+"*.log" +
				@" WHERE TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%' GROUP BY cs-uri-stem ORDER BY cs-uri-stem";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 * Another indicator may be how much time the server spent processing the request. It is not uncommon for exploits to take an unusually large amount of time or even timeout completely. The following query reports on time taken
		 */

		public DataSet TimeSpentProcessing(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" cs-uri-stem As URI, Count(*) as Hits, AVG(time-taken) AS Avg, Max(time-taken) AS Max, Min(time-taken) AS Min, Sum(time-taken) AS Total from "+log_dir+"*.log" +
				@" Where TO_LOWERCASE(cs-uri-stem) LIKE '%.aspx%' or TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%' GROUP BY cs-uri-stem ORDER BY cs-uri-stem";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}

		/*
		 * Sometimes it is possible to identify an attack script by looking at the HTTP User-Agent header sent by the client. You can get a list of non-standard User-Agent strings with this query:
		 */
		public DataSet NonStandardUserAgent(String X)
		{
			string strSQL = null;
			string log_dir = "";
			if(X==null || String.Compare(X,"")==0)
			{
				X = "10";
			}	
			log_dir = ConfigurationSettings.AppSettings["Log_Dir"];

			
			strSQL = @"SELECT Top "+X+" DISTINCT cs(User-Agent) as Agent from "+log_dir+"*.log" +
				@" Where TO_LOWERCASE(cs(User-Agent)) NOT LIKE '%mozilla%' AND TO_LOWERCASE(cs(User-Agent)) NOT LIKE '%opera%' ORDER BY cs(User-Agent)";


			//strSQL = @"SELECT  Count(c-ip) as Total from "+log_dir+"*.log";

			DataSet sets = this.parseLog(strSQL);
			return sets;
		}
	}
}
		